package com.yikome.report.service;

import java.math.BigDecimal;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.yikome.order.enums.OrderStatus;
import com.yikome.report.dto.AffilateReportDTO;
import com.yikome.report.request.AffilateReportRequest;
import com.google.common.collect.ImmutableMap;

import cn.hutool.core.util.StrUtil;

@Service
public class ReportService {
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ;

	public Collection<AffilateReportDTO> findAffilateReport(AffilateReportRequest affilateReportRequest) {
		
		StringBuilder paramSQL = new StringBuilder("	WHERE 1 = 1 AND o_order.yn = 1 \n");
		Map<String , Object> paramMap = new HashMap<>();
		
		paramSQL.append("	AND o_order.`status` >= :orderStatus \n");
		paramMap.put("orderStatus", OrderStatus.ORDER_REVIEW.getStatus());
		
		if(affilateReportRequest.getShopId() != null) {
			paramSQL.append("	AND o_order_ext.shop_id = :shopId \n");
			paramMap.put("shopId", affilateReportRequest.getShopId());
		}
		paramSQL.append("	AND o_order.created_at BETWEEN FROM_UNIXTIME(:startTime) AND FROM_UNIXTIME(:endTime) \n");
		paramMap.put("startTime", affilateReportRequest.getStartTime());
		paramMap.put("endTime", affilateReportRequest.getEndTime());
		
		paramMap.put("hkdus", BigDecimal.ONE.divide(affilateReportRequest.getHk$() , 4 , BigDecimal.ROUND_HALF_DOWN));
		paramMap.put("twdus", BigDecimal.ONE.divide(affilateReportRequest.getTwd$() , 4 , BigDecimal.ROUND_HALF_DOWN));
		paramMap.put("cnyus", BigDecimal.ONE.divide(affilateReportRequest.getCny$() , 4 , BigDecimal.ROUND_HALF_DOWN));
		
		String contentSQL = "SELECT \n" + 
							"	vo.affiliate, vo.payment, COUNT(vo.`code`)order_count, SUM(vo.order_sku_count) order_sku_count,  \n" + 
							"	ROUND(SUM(vo.order_sku_count)/COUNT(vo.`code`),4) average_order_sku_count, ROUND(SUM(vo.order_price),4) order_price,  \n" + 
							"	ROUND(SUM(vo.order_price)/COUNT(vo.`code`),4) average_order_price, ROUND(SUM(vo.purchase_prices),4) purchase_price \n" + 
							"FROM \n" + 
							"( \n" + 
							"	SELECT  \n" + 
							"		o_order.`code`, (o_order.price * IF(o_order.currency = 'HKD', :hkdus , :twdus)) order_price, IF(o_order.payment = 1 , 'COD' , 'STRIPE') payment, IFNULL(o_order_ext.affiliate,'--') affiliate, \n" + 
							"		(SELECT SUM(o_order_product.quantity) FROM o_order_product WHERE o_order_product.order_code = o_order.`code`)order_sku_count, \n" + 
							"		(SELECT SUM(o_order_product.quantity * p_product_sku.purchase_price) * :cnyus FROM o_order_product LEFT JOIN p_product_sku ON (o_order_product.product_sku_id = p_product_sku.id) WHERE o_order_product.order_code = o_order.`code`)purchase_prices \n" + 
							"	FROM o_order \n" + 
							"		LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
							paramSQL +
							") vo \n" + 
							"GROUP BY vo.affiliate, vo.payment \n" + 
							"ORDER BY COUNT(vo.`code`) DESC \n" ; 
		
		List<AffilateReportDTO> affilateReportDTOs = jdbcTemplate.query(contentSQL, paramMap, new BeanPropertyRowMapper<>(AffilateReportDTO.class));
		
		/**
		 * 整理需要的数据:
		 * 投放人数据汇总:
		 * 总销售数据/STRIPE总销售数据/COD总销售数据
		 */
		AffilateReportDTO codTotal = new AffilateReportDTO("codTotal", "COD", 0, 0, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO);
		AffilateReportDTO stripeTotal = new AffilateReportDTO("stripeTotal", "STRIPE", 0, 0, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO);
		AffilateReportDTO allTotal = new AffilateReportDTO("allTotal", "COD&STRIPE", 0, 0, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO);
		
		Map<String , AffilateReportDTO> affilateMap = new LinkedHashMap<>();
		
		for (AffilateReportDTO affilateReportDTO : affilateReportDTOs) {
			if(StrUtil.equals(affilateReportDTO.getPayment(), "COD")) {
				codTotal.setOrderCount(codTotal.getOrderCount() + affilateReportDTO.getOrderCount());
				codTotal.setOrderSkuCount(codTotal.getOrderSkuCount() + affilateReportDTO.getOrderSkuCount());
				codTotal.setOrderPrice(codTotal.getOrderPrice().add(affilateReportDTO.getOrderPrice()));
				codTotal.setPurchasePrice(codTotal.getPurchasePrice().add(affilateReportDTO.getPurchasePrice()));
			}else if(StrUtil.equals(affilateReportDTO.getPayment(), "STRIPE")) {
				stripeTotal.setOrderCount(stripeTotal.getOrderCount() + affilateReportDTO.getOrderCount());
				stripeTotal.setOrderSkuCount(stripeTotal.getOrderSkuCount() + affilateReportDTO.getOrderSkuCount());
				stripeTotal.setOrderPrice(stripeTotal.getOrderPrice().add(affilateReportDTO.getOrderPrice()));
				stripeTotal.setPurchasePrice(stripeTotal.getPurchasePrice().add(affilateReportDTO.getPurchasePrice()));
			}
			
			if(!affilateMap.containsKey(affilateReportDTO.getAffiliate())) {
				affilateMap.put(affilateReportDTO.getAffiliate(), new AffilateReportDTO(affilateReportDTO.getAffiliate(), "COD&STRIPE", 0, 0, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO));
			}
			AffilateReportDTO tmpAffilate = affilateMap.get(affilateReportDTO.getAffiliate());
			tmpAffilate.setOrderCount(tmpAffilate.getOrderCount() + affilateReportDTO.getOrderCount());
			tmpAffilate.setOrderSkuCount(tmpAffilate.getOrderSkuCount() + affilateReportDTO.getOrderSkuCount());
			tmpAffilate.setOrderPrice(tmpAffilate.getOrderPrice().add(affilateReportDTO.getOrderPrice()));
			tmpAffilate.setPurchasePrice(tmpAffilate.getPurchasePrice().add(affilateReportDTO.getPurchasePrice()));
			
			allTotal.setOrderCount(allTotal.getOrderCount() + affilateReportDTO.getOrderCount());
			allTotal.setOrderSkuCount(allTotal.getOrderSkuCount() + affilateReportDTO.getOrderSkuCount());
			allTotal.setOrderPrice(allTotal.getOrderPrice().add(affilateReportDTO.getOrderPrice()));
			allTotal.setPurchasePrice(allTotal.getPurchasePrice().add(affilateReportDTO.getPurchasePrice()));
		}
		
		codTotal.setAverageOrderPrice(codTotal.getOrderPrice().divide(findBigDecimalDefault(codTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		codTotal.setAverageOrderSkuCount(BigDecimal.valueOf(codTotal.getOrderSkuCount()).divide(findBigDecimalDefault(codTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		
		stripeTotal.setAverageOrderPrice(stripeTotal.getOrderPrice().divide(findBigDecimalDefault(stripeTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		stripeTotal.setAverageOrderSkuCount(BigDecimal.valueOf(stripeTotal.getOrderSkuCount()).divide(findBigDecimalDefault(stripeTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		
		allTotal.setAverageOrderPrice(allTotal.getOrderPrice().divide(findBigDecimalDefault(allTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		allTotal.setAverageOrderSkuCount(BigDecimal.valueOf(allTotal.getOrderSkuCount()).divide(findBigDecimalDefault(allTotal.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		
		for(Map.Entry<String, AffilateReportDTO> map : affilateMap.entrySet()) {
			AffilateReportDTO affilateReportDTO = map.getValue();
			affilateReportDTO.setAverageOrderPrice(affilateReportDTO.getOrderPrice().divide(findBigDecimalDefault(affilateReportDTO.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
			affilateReportDTO.setAverageOrderSkuCount(BigDecimal.valueOf(affilateReportDTO.getOrderSkuCount()).divide(findBigDecimalDefault(affilateReportDTO.getOrderCount() , 1) , 4 , BigDecimal.ROUND_HALF_DOWN));
		}
		
		affilateMap.put("codTotal", codTotal);
		affilateMap.put("stripeTotal", stripeTotal);
		affilateMap.put("allTotal", allTotal);
		
		for(Map.Entry<String, AffilateReportDTO> map : affilateMap.entrySet()) {
			AffilateReportDTO affilateReportDTO = map.getValue();
			affilateReportDTO.setAverageOrderPrice(affilateReportDTO.getAverageOrderPrice().setScale(2, BigDecimal.ROUND_HALF_DOWN));
			affilateReportDTO.setAverageOrderSkuCount(affilateReportDTO.getAverageOrderSkuCount().setScale(2, BigDecimal.ROUND_HALF_DOWN));
			affilateReportDTO.setOrderPrice(affilateReportDTO.getOrderPrice().setScale(2, BigDecimal.ROUND_HALF_DOWN));
			affilateReportDTO.setPurchasePrice(affilateReportDTO.getPurchasePrice().setScale(2, BigDecimal.ROUND_HALF_DOWN));
		}
		
		return affilateMap.values();
	}
	
	private BigDecimal findBigDecimalDefault(Integer value , Integer defaultValue) {
		if(value != 0) return BigDecimal.valueOf(value);
		return BigDecimal.valueOf(defaultValue);
	}

	/**
	 * 时间范围内的订单购买者在开始时间之前购买过的,就复购,
	 * 复购率计算方式: 之前购买过的独立人单数/时间范围内单数
	 */
	public BigDecimal findRepeatOrderrate(AffilateReportRequest affilateReportRequest) {
		
		Map<String, Long> paramMap = new HashMap<>();
		paramMap.put("startTime", affilateReportRequest.getStartTime());
		paramMap.put("endTime", affilateReportRequest.getEndTime());
		StringBuilder paramSQL = new StringBuilder("WHERE 1 = 1 AND o_order.yn = 1 AND o_order.`status` > 0 \n");
		if(affilateReportRequest.getShopId() != null) {
			paramSQL.append("	AND o_order_ext.shop_id = :shopId \n");
			paramMap.put("shopId", affilateReportRequest.getShopId());
		}
		
		String timeRangeTotalOrderSQL = "SELECT  \n" + 
										"	COUNT(DISTINCT o_order_address.tel) \n" + 
										"FROM o_order \n" + 
										"	LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n" + 
										"	LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
										paramSQL + 
										"AND o_order.created_at BETWEEN FROM_UNIXTIME(:startTime) AND FROM_UNIXTIME(:endTime) \n" ; 
		Integer timeRangeTotalOrder = jdbcTemplate.queryForObject(timeRangeTotalOrderSQL, paramMap, Integer.class);
		
		String timeBeforeTotalOrderSQL ="SELECT  \n" + 
										"	COUNT(DISTINCT o_order_address.tel) \n" + 
										"FROM o_order \n" + 
										"	LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n" + 
										"	LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
										paramSQL + 
										"AND o_order.created_at < FROM_UNIXTIME(:startTime) \n" + 
										"AND o_order_address.tel IN  \n" + 
										"( \n" + 
										"	SELECT  \n" + 
										"		DISTINCT o_order_address.tel \n" + 
										"	FROM o_order \n" + 
										"		LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n" + 
										"		LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
										paramSQL + 
										"	AND o_order.created_at BETWEEN FROM_UNIXTIME(:startTime) AND FROM_UNIXTIME(:endTime) \n" + 
										") \n" ; 
		Integer timeBeforeTotalOrder = jdbcTemplate.queryForObject(timeBeforeTotalOrderSQL, paramMap, Integer.class);
		
		return BigDecimal.valueOf(timeBeforeTotalOrder).divide(findBigDecimalDefault(timeRangeTotalOrder , 1), 4, BigDecimal.ROUND_HALF_DOWN);
	}
}
